﻿using AppsModel.Data_Layer;
using AppsModel.Models;
using MVC_basic.Data_Layer.Interface;
using MVC_basic.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
namespace MVC_basic.Data_Layer
{
    public class dbApp : IdbApp
    {
        public App addCanvasApp(string name, string logoUrl, string siteUrl, int userId, int canvasWidth, int canvasHeight)
        {
            SqlConnection conn = null;
            SqlDataReader rdr = null;
            CanvasApp app = null;
            try
            {
                conn = Helper.createConnection();

                // 1. create a command object identifying
                // the stored procedure
                SqlCommand cmd = new SqlCommand("addCanvasApp", conn);

                // 2. set the command object so it knows
                // to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // 3. add parameter to command, which
                // will be passed to the stored procedure
                cmd.Parameters.Add(new SqlParameter("@Name", name));
                cmd.Parameters.Add(new SqlParameter("@logoUrl", logoUrl));
                cmd.Parameters.Add(new SqlParameter("@siteUrl", siteUrl));
                cmd.Parameters.Add(new SqlParameter("@userId", userId));
                cmd.Parameters.Add(new SqlParameter("@canvasWidth", canvasWidth));
                cmd.Parameters.Add(new SqlParameter("@canvasHeight", canvasHeight));

                // execute the command
                rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    app = new CanvasApp
                    {
                        id = rdr.GetInt32(0),
                        name = name,
                        logoUrl = logoUrl,
                        url = siteUrl,
                        userId = userId,
                        canvasWidth = canvasWidth,
                        canvasHeight = canvasHeight
                    };
                }
            }
            finally
            {
                if (conn != null)
                    conn.Close();
                if (rdr != null)
                    rdr.Close();
            }
            return app;
        }

        public App addCajaApp(string name, string logoUrl, string acceptedUrl, int userId)
        {
            SqlConnection conn = null;
            SqlDataReader rdr = null;
            CajaApp app = null;
            try
            {
                conn = Helper.createConnection();

                // 1. create a command object identifying
                // the stored procedure
                SqlCommand cmd = new SqlCommand("addCajaApp", conn);

                // 2. set the command object so it knows
                // to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // 3. add parameter to command, which
                // will be passed to the stored procedure
                cmd.Parameters.Add(new SqlParameter("@Name", name));
                cmd.Parameters.Add(new SqlParameter("@logoUrl", logoUrl));
                cmd.Parameters.Add(new SqlParameter("@userId", userId));
                cmd.Parameters.Add(new SqlParameter("@acceptedUrl", acceptedUrl));

                // execute the command
                rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    app = new CajaApp
                    {
                        id = rdr.GetInt32(0),
                        name = name,
                        logoUrl = logoUrl,
                        userId = userId,
                        acceptedUrl = acceptedUrl
                    };
                }
            }
            finally
            {
                if (conn != null)
                    conn.Close();
                if (rdr != null)
                    rdr.Close();
            }
            return app;
        }
        
        public void updateCanvasApp(int id, string name, string logoUrl, string siteUrl, int userId, int canvasWidth, int canvasHeight)
        {
            SqlConnection conn = null;

            try
            {
                conn = Helper.createConnection();

                // 1. create a command object identifying
                // the stored procedure
                SqlCommand cmd = new SqlCommand("updateApp", conn);

                // 2. set the command object so it knows
                // to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // 3. add parameter to command, which
                // will be passed to the stored procedure
                cmd.Parameters.Add(new SqlParameter("@id", id));
                cmd.Parameters.Add(new SqlParameter("@Name", name));
                cmd.Parameters.Add(new SqlParameter("@logoUrl", logoUrl));
                cmd.Parameters.Add(new SqlParameter("@siteUrl", siteUrl));
                cmd.Parameters.Add(new SqlParameter("@canvasWidth", canvasWidth));
                cmd.Parameters.Add(new SqlParameter("@canvasHeight", canvasHeight));

                // execute the command
                cmd.ExecuteNonQuery();

            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }	
        }

        public void updateCajaApp(int id, string name, string logoUrl, string acceptedUrl)
        {
            SqlConnection conn = null;

            try
            {
                conn = Helper.createConnection();

                // 1. create a command object identifying
                // the stored procedure
                SqlCommand cmd = new SqlCommand("updateApp", conn);

                // 2. set the command object so it knows
                // to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // 3. add parameter to command, which
                // will be passed to the stored procedure
                cmd.Parameters.Add(new SqlParameter("@id", id));
                cmd.Parameters.Add(new SqlParameter("@Name", name));
                cmd.Parameters.Add(new SqlParameter("@logoUrl", logoUrl));
                cmd.Parameters.Add(new SqlParameter("@acceptedUrl", acceptedUrl));
                // execute the command
                cmd.ExecuteNonQuery();

            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }	
        }
        
        public void deleteApp(int AppId)
        {
            SqlConnection conn = null;

            try
            {
                conn = Helper.createConnection();

                // 1. create a command object identifying
                // the stored procedure
                SqlCommand cmd = new SqlCommand("DeleteApp", conn);

                // 2. set the command object so it knows
                // to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // 3. add parameter to command, which
                // will be passed to the stored procedure
                cmd.Parameters.Add(new SqlParameter("@AppId", AppId));

                // execute the command
                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }	
        }
        
        public List<App> getApps()
        {
            SqlConnection conn = null;
            SqlDataReader rdr = null;
            List<App> apps = new List<App>();
            try
            {
                conn = Helper.createConnection();

                // 1. create a command object identifying
                // the stored procedure
                SqlCommand cmd = new SqlCommand("getApps", conn);

                // 2. set the command object so it knows
                // to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // execute the command
			    rdr = cmd.ExecuteReader();

                
			    // iterate through results, printing each to console
			    while (rdr.Read())
			    {
                    App temp = new App
                    {
                        id = rdr.GetInt32(0),
                        name = rdr.GetString(1),
                        logoUrl = rdr.GetString(2),
                        //url = rdr.GetString(5),
                        userId = rdr.GetInt32(3),
                        accepted = rdr.GetInt32(5) == 1 ? true : false
                        //canvasWidth = rdr.GetInt32(6),
                        //canvasHeight = rdr.GetInt32(7)
                    };
                    if (rdr.GetInt32(4) == 1)
                        temp.appType = "Canvas";
                    else if (rdr.GetInt32(4) == 2)
                        temp.appType = "Caja";
                    apps.Add(temp);
			    }
            }
            finally
            {
                if (conn != null)
                    conn.Close();
                if (rdr != null)
                    rdr.Close();
            }
            return apps;
        }

        public List<App> getAppsByUser(int UserId)
        {
            SqlConnection conn = null;
            SqlDataReader rdr = null;
            List<App> apps = new List<App>();
            try
            {
                conn = Helper.createConnection();

                SqlCommand cmd = new SqlCommand("getAppsByUser", conn);

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@UserId", UserId));

                rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    App temp = new App
                    {
                        id = rdr.GetInt32(0),
                        name = rdr.GetString(1),
                        logoUrl = rdr.GetString(2),
                        userId = rdr.GetInt32(3)
                    };
                    if (rdr.GetInt32(4) == 1)
                        temp.appType = "Canvas";
                    else if (rdr.GetInt32(4) == 2)
                        temp.appType = "Caja";
                    apps.Add(temp);
                }
            }
            finally
            {
                if (conn != null)
                    conn.Close();
                if (rdr != null)
                    rdr.Close();
            }
            return apps;
        }

        public App getApp(int id)
        {
            SqlConnection conn = null;
            SqlDataReader rdr = null;
            App app = null;
            try
            {
                conn = Helper.createConnection();

                // 1. create a command object identifying
                // the stored procedure
                SqlCommand cmd = new SqlCommand("getApp", conn);

                // 2. set the command object so it knows
                // to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // 3. add parameter to command, which
                // will be passed to the stored procedure
                cmd.Parameters.Add(new SqlParameter("@id", id));

                // execute the command
                rdr = cmd.ExecuteReader();

                // iterate through results, printing each to console
                while (rdr.Read())
                {
                    if (rdr.GetInt32(4) == 1)
                    {
                        app = new CanvasApp
                        {
                            id = rdr.GetInt32(0),
                            name = rdr.GetString(1),
                            logoUrl = rdr.GetString(2),
                            appType = "Canvas",
                            accepted = rdr.GetInt32(5) == 1 ? true : false,
                            url = rdr.GetString(7),
                            userId = rdr.GetInt32(3),
                            canvasWidth = rdr.GetInt32(8),
                            canvasHeight = rdr.GetInt32(9)
                        };
                    }
                    else if (rdr.GetInt32(4) == 2)
                    {
                        app = new CajaApp
                        {
                            id = rdr.GetInt32(0),
                            name = rdr.GetString(1),
                            logoUrl = rdr.GetString(2),
                            appType = "Caja",
                            accepted = rdr.GetInt32(5) == 1 ? true : false,
                            userId = rdr.GetInt32(3),
                            acceptedUrl = rdr.GetString(7)
                        };
                    }
                }
            }
            finally
            {
                if (conn != null)
                    conn.Close();
                if (rdr != null)
                    rdr.Close();
            }
            return app;
        }

        public void installApp(int AppId, int UserId)
        {
            SqlConnection conn = null;

            try
            {
                conn = Helper.createConnection();

                // 1. create a command object identifying
                // the stored procedure
                SqlCommand cmd = new SqlCommand("InstallApp", conn);

                // 2. set the command object so it knows
                // to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // 3. add parameter to command, which
                // will be passed to the stored procedure
                cmd.Parameters.Add(new SqlParameter("@AppId", AppId));
                cmd.Parameters.Add(new SqlParameter("@UserId", UserId));

                // execute the command
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {

            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }	
        }

        public void uninstallApp(int AppId, int UserId)
        {
            SqlConnection conn = null;

            try
            {
                conn = Helper.createConnection();

                // 1. create a command object identifying
                // the stored procedure
                SqlCommand cmd = new SqlCommand("UninstallApp", conn);

                // 2. set the command object so it knows
                // to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // 3. add parameter to command, which
                // will be passed to the stored procedure
                cmd.Parameters.Add(new SqlParameter("@AppId", AppId));
                cmd.Parameters.Add(new SqlParameter("@UserId", UserId));

                // execute the command
                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }	
        }

        public List<App> getInstalledApps(int UserId)
        {
            SqlConnection conn = null;
            SqlDataReader rdr = null;
            App app = null;
            List<App> apps = new List<App>();
            try
            {
                conn = Helper.createConnection();

                // 1. create a command object identifying
                // the stored procedure
                SqlCommand cmd = new SqlCommand("getInstalledApps", conn);

                // 2. set the command object so it knows
                // to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // 3. add parameter to command, which
                // will be passed to the stored procedure
                cmd.Parameters.Add(new SqlParameter("@UserId", UserId));

                // execute the command
                rdr = cmd.ExecuteReader();

                // iterate through results, printing each to console
                while (rdr.Read())
                {
                    app = new App
                    {
                        id = rdr.GetInt32(0),
                        name = rdr.GetString(1),
                        logoUrl = rdr.GetString(2),
                        //url = rdr.GetString(5),
                        userId = rdr.GetInt32(3),
                        //canvasWidth = rdr.GetInt32(6),
                        //canvasHeight = rdr.GetInt32(7)
                    };
                    if (rdr.GetInt32(4) == 1)
                        app.appType = "Canvas";
                    else if (rdr.GetInt32(4) == 2)
                        app.appType = "Caja";
                    apps.Add(app);
                }
            }
            finally
            {
                if (conn != null)
                    conn.Close();
                if (rdr != null)
                    rdr.Close();
            }
            return apps;
        }
    }
}